/**
 * 
 */
package com.magus.project.yddemo.service;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.magus.framework.core.persistence.JpaSearchUtils;
import com.magus.framework.core.persistence.SearchFilter;
import com.magus.framework.core.repository.BaseRepository;
import com.magus.framework.core.service.BaseService;
import com.magus.project.yddemo.entity.IndicatorMapperData;
import com.magus.project.yddemo.entity.IndicatorMapperReport5;
import com.magus.project.yddemo.repository.IndicatorMapperReport5Repository;

/**
 * @author wenyu.han
 * @date 2018-04-04 15:39:00
 */
@Service
@Transactional(readOnly = true)
public class IndicatorMapperReport5Service extends BaseService<IndicatorMapperReport5, String> {
	@Autowired
	private IndicatorMapperReport5Repository reportRepository;

	@Autowired
	private IndicatorMapperDataService mapperDataService;

	@Override
	protected BaseRepository<IndicatorMapperReport5, String> getBaseRepository() {
		return reportRepository;
	}

	// 数据起始行号
	private static final Integer ROW_INDEX = 13;

	/**
	 * 每行的固定列用##符号分开
	 */
	private static final String[] ALL_ROW_NAME = new String[] { "合    计##00##null", "其中：燃煤##01##null", "电厂明细##02##null",
			"广东电力发展股份有限公司沙角A电厂##0201##73148621-9", "广东粤电集团有限公司沙角C电厂##0202##74369834-X",
			"广东省粤电集团有限公司珠海发电厂##0203##70812907-X", "广东粤华发电有限责任公司##0204##19032084-8", "湛江电力有限公司##0205##23210093-2",
			"广东省韶关粤江发电有限责任公司##0206##70752208-0", "茂名臻能热电有限公司##0207##76656459-8", "广东粤电云河发电有限公司##0208##69475944-0",
			"广东省粤泷发电有限责任公司##0209##61785108-3", "广东珠海金湾发电有限公司##0210##71785911-1", "广东红海湾发电有限公司##0211##77307022-X",
			"广东粤电靖海发电有限公司##0212##76061818-8", "广东惠州平海发电厂有限公司##0213##791172465", "湛江中粤能源有限公司##0214##61806419-6",
			"广东粤电大埔发电有限公司##0215##06145024-0", "贵州粤黔电力有限责任公司盘南发电厂##0216##61806419-6", "广东惠州天然气发电有限公司##0217##791172465",
			"深圳市广前电力有限公司##0218##76380066-3"

	};

	/**
	 * 计算发电煤耗的发电量全部指标
	 */
	static final String[] FDL_ALL = new String[] { "SJA_FDL_M_M_M_SUM_M_MANL", "SJC_FDL_M_M_M_SUM_M_MANL",
			"ZHP_FDL_M_M_M_SUM_M_MANL", "HPP_FDL_M_M_M_SUM_M_MANL", "ZJP_FDL_M_M_M_SUM_M_MANL",
			"SGY_FDL_M_M_M_SUM_M_MANL", "MMP_FDL_M_M_M_SUM_M_MANL", "YHP_FDL_M_M_M_SUM_M_MANL",
			"YLP_FDL_M_M_M_SUM_M_MANL", "JWP_FDL_M_M_M_SUM_M_MANL", "HHW_FDL_M_M_M_SUM_M_MANL",
			"JHP_FDL_M_M_M_SUM_M_MANL", "PHP_FDL_M_M_M_SUM_M_MANL", "GZH_FDL_M_M_M_SUM_M_MANL",
			"TSP_FDL_M_M_M_SUM_M_MANL", "DPP_FDL_M_M_M_SUM_M_MANL", "HZL_FDL_M_M_M_SUM_M_MANL",
			"GQL_FDL_M_M_M_SUM_M_MANL" };

	/**
	 * 计算发电煤耗的发电量-燃煤
	 */
	static final String[] FDL_RM = new String[] { "HZL_FDL_M_M_M_SUM_M_MANL", "GQL_FDL_M_M_M_SUM_M_MANL" };

	/**
	 * 计算供电煤耗的供电量全部指标
	 */
	static final String[] SCCYDL_ALL = new String[] { "SJA_SCCYDL_M_M_M_SUM_M_MANL", "SJC_SCCYDL_M_M_M_SUM_M_MANL",
			"ZHP_SCCYDL_M_M_M_SUM_M_MANL", "HPP_SCCYDL_M_M_M_SUM_M_MANL", "ZJP_SCCYDL_M_M_M_SUM_M_MANL",
			"SGY_SCCYDL_M_M_M_SUM_M_MANL", "MMP_SCCYDL_M_M_M_SUM_M_MANL", "YHP_SCCYDL_M_M_M_SUM_M_MANL",
			"YLP_SCCYDL_M_M_M_SUM_M_MANL", "JWP_SCCYDL_M_M_M_SUM_M_MANL", "HHW_SCCYDL_M_M_M_SUM_M_MANL",
			"JHP_SCCYDL_M_M_M_SUM_M_MANL", "PHP_SCCYDL_M_M_M_SUM_M_MANL", "GZH_SCCYDL_M_M_M_SUM_M_MANL",
			"TSP_SCCYDL_M_M_M_SUM_M_MANL", "DPP_SCCYDL_M_M_M_SUM_M_MANL", "HZL_SCCYDL_M_M_M_SUM_M_MANL",
			"GQL_SCCYDL_M_M_M_SUM_M_MANL"

	};

	/**
	 * 计算供电煤耗的供电量-燃煤
	 */
	static final String[] SCCYDL_RM = new String[] { "HZL_SCCYDL_M_M_M_SUM_M_MANL", "GQL_SCCYDL_M_M_M_SUM_M_MANL" };

	/**
	 * 发电消耗标准煤量（吨）-合计
	 */
	static final String[] BZML_HJ = new String[] { "SJA_FDZJZB_M_M_M_SUM_M_MANL", "SJC_FDZJZB_M_M_M_SUM_M_MANL",
			"ZHP_FDZJZB_M_M_M_SUM_M_MANL", "HPP_FDZJZB_M_M_M_SUM_M_MANL", "ZJP_FDZJZB_M_M_M_SUM_M_MANL",
			"SGY_FDZJZB_M_M_M_SUM_M_MANL", "MMP_FDZJZB_M_M_M_SUM_M_MANL", "YHP_FDZJZB_M_M_M_SUM_M_MANL",
			"YLP_FDZJZB_M_M_M_SUM_M_MANL", "JWP_FDZJZB_M_M_M_SUM_M_MANL", "HHW_FDZJZB_M_M_M_SUM_M_MANL",
			"JHP_FDZJZB_M_M_M_SUM_M_MANL", "PHP_FDZJZB_M_M_M_SUM_M_MANL", "GZH_FDZJZB_M_M_M_SUM_M_MANL",
			"TSP_FDZJZB_M_M_M_SUM_M_MANL", "DPP_FDZJZB_M_M_M_SUM_M_MANL", "HZL_FDZJZB_M_M_M_SUM_M_MANL",
			"GQL_FDZJZB_M_M_M_SUM_M_MANL"

	};

	/**
	 * 发电消耗标准煤量（吨）-合计-燃煤
	 */
	static final String[] BZML_HJ_RM = new String[] { "HZL_FDZJZB_M_M_M_SUM_M_MANL", "GQL_FDZJZB_M_M_M_SUM_M_MANL" };

	/**
	 * 发电消耗标准煤量-煤折
	 */
	static final String[] BZML_MZ = new String[] { "SJA_FDHYM_M_M_M_SUM_M_CTST", "SJC_FDHYM_M_M_M_SUM_M_CTST",
			"ZHP_FDHYM_M_M_M_SUM_M_CTST", "HPP_FDHYM_M_M_M_SUM_M_CTST", "ZJP_FDHYM_M_M_M_SUM_M_CTST",
			"SGY_FDHYM_M_M_M_SUM_M_CTST", "MMP_FDHYM_M_M_M_SUM_M_CTST", "YHP_FDHYM_M_M_M_SUM_M_CTST",
			"YLP_FDHYM_M_M_M_SUM_M_CTST", "JWP_FDHYM_M_M_M_SUM_M_CTST", "HHW_FDHYM_M_M_M_SUM_M_CTST",
			"JHP_FDHYM_M_M_M_SUM_M_CTST", "PHP_FDHYM_M_M_M_SUM_M_CTST", "GZH_FDHYM_M_M_M_SUM_M_CTST",
			"TSP_FDHYM_M_M_M_SUM_M_CTST", "DPP_FDHYM_M_M_M_SUM_M_CTST", "", ""

	};

	/**
	 * 发电消耗标准煤量-煤折-燃煤
	 */
	static final String[] BZML_MZ_RM = new String[] { "", "" };

	/**
	 * 发电消耗标准煤量-油折
	 */
	static final String[] BZML_YZ = new String[] { "SJA_FDYYZB_M_M_M_PAV_M_CTST", "SJC_FDYYZB_M_M_M_PAV_M_CTST",
			"ZHP_FDYYZB_M_M_M_PAV_M_CTST", "HPP_FDYYZB_M_M_M_PAV_M_CTST", "ZJP_FDYYZB_M_M_M_PAV_M_CTST",
			"SGY_FDYYZB_M_M_M_PAV_M_CTST", "MMP_FDYYZB_M_M_M_PAV_M_CTST", "YHP_FDYYZB_M_M_M_PAV_M_CTST",
			"YLP_FDYYZB_M_M_M_PAV_M_CTST", "JWP_FDYYZB_M_M_M_PAV_M_CTST", "HHW_FDYYZB_M_M_M_PAV_M_CTST",
			"JHP_FDYYZB_M_M_M_PAV_M_CTST", "PHP_FDYYZB_M_M_M_PAV_M_CTST", "GZH_FDYYZB_M_M_M_PAV_M_CTST",
			"TSP_FDYYZB_M_M_M_PAV_M_CTST", "DPP_FDYYZB_M_M_M_PAV_M_CTST", "", ""

	};

	/**
	 * 发电消耗标准煤量-油折-燃煤
	 */
	static final String[] BZML_YZ_RM = new String[] { "", "" };

	/**
	 * 发电消耗标准煤量-天然气折
	 */
	static final String[] BZML_TRQZ = new String[] { "SJA_FDYYZB_M_M_M_PAV_M_CTST", "SJC_FDYYZB_M_M_M_PAV_M_CTST",
			"ZHP_FDYYZB_M_M_M_PAV_M_CTST", "HPP_FDYYZB_M_M_M_PAV_M_CTST", "ZJP_FDYYZB_M_M_M_PAV_M_CTST",
			"SGY_FDYYZB_M_M_M_PAV_M_CTST", "MMP_FDYYZB_M_M_M_PAV_M_CTST", "YHP_FDYYZB_M_M_M_PAV_M_CTST",
			"YLP_FDYYZB_M_M_M_PAV_M_CTST", "JWP_FDYYZB_M_M_M_PAV_M_CTST", "HHW_FDYYZB_M_M_M_PAV_M_CTST",
			"JHP_FDYYZB_M_M_M_PAV_M_CTST", "PHP_FDYYZB_M_M_M_PAV_M_CTST", "GZH_FDYYZB_M_M_M_PAV_M_CTST",
			"TSP_FDYYZB_M_M_M_PAV_M_CTST", "DPP_FDYYZB_M_M_M_PAV_M_CTST", "HZL_FDRQZB_M_M_M_SUM_M_CTST",
			"GQL_FDRQZB_M_M_M_SUM_M_CTST"

	};

	/**
	 * 发电消耗标准煤量-天然气折-燃煤
	 */
	static final String[] BZML_TRQZ_RM = new String[] { "HZL_FDRQZB_M_M_M_SUM_M_CTST", "GQL_FDRQZB_M_M_M_SUM_M_CTST" };

	/**
	 * 发电消耗标准煤量-其它
	 */
	static final String[] BZML_QT = new String[] { "SJA_FDQTZB_M_M_M_SUM_M_CTST", "SJC_FDQTZB_M_M_M_SUM_M_CTST",
			"ZHP_FDQTZB_M_M_M_SUM_M_CTST", "HPP_FDQTZB_M_M_M_SUM_M_CTST", "ZJP_FDQTZB_M_M_M_SUM_M_CTST",
			"SGY_FDQTZB_M_M_M_SUM_M_CTST", "MMP_FDQTZB_M_M_M_SUM_M_CTST", "YHP_FDQTZB_M_M_M_SUM_M_CTST",
			"YLP_FDQTZB_M_M_M_SUM_M_CTST", "JWP_FDQTZB_M_M_M_SUM_M_CTST", "HHW_FDQTZB_M_M_M_SUM_M_CTST",
			"JHP_FDQTZB_M_M_M_SUM_M_CTST", "PHP_FDQTZB_M_M_M_SUM_M_CTST", "GZH_FDQTZB_M_M_M_SUM_M_CTST",
			"TSP_FDQTZB_M_M_M_SUM_M_CTST", "DPP_FDQTZB_M_M_M_SUM_M_CTST", "HZL_FDQTZB_M_M_M_SUM_M_CTST",
			"GQL_FDQTZB_M_M_M_SUM_M_CTST"

	};

	/**
	 * 发电消耗标准煤量-其它-燃煤
	 */
	static final String[] BZML_QT_RM = new String[] { "HZL_FDQTZB_M_M_M_SUM_M_CTST", "GQL_FDQTZB_M_M_M_SUM_M_CTST" };

	/**
	 * 耗用原煤
	 */
	static final String[] HYYM = new String[] { "SJA_FDHYM_M_M_M_SUM_M_CTST", "SJC_FDHYM_M_M_M_SUM_M_CTST",
			"ZHP_FDHYM_M_M_M_SUM_M_CTST", "HPP_FDHYM_M_M_M_SUM_M_CTST", "ZJP_FDHYM_M_M_M_SUM_M_CTST",
			"SGY_FDHYM_M_M_M_SUM_M_CTST", "MMP_FDHYM_M_M_M_SUM_M_CTST", "YHP_FDHYM_M_M_M_SUM_M_CTST",
			"YLP_FDHYM_M_M_M_SUM_M_CTST", "JWP_FDHYM_M_M_M_SUM_M_CTST", "HHW_FDHYM_M_M_M_SUM_M_CTST",
			"JHP_FDHYM_M_M_M_SUM_M_CTST", "PHP_FDHYM_M_M_M_SUM_M_CTST", "GZH_FDHYM_M_M_M_SUM_M_CTST",
			"TSP_FDHYM_M_M_M_SUM_M_CTST", "DPP_FDHYM_M_M_M_SUM_M_CTST", "", ""

	};

	/**
	 * 耗用原煤-燃煤
	 */
	static final String[] HYYM_RM = new String[] { "", "" };

	/**
	 * 耗用燃油
	 */
	static final String[] HYRY = new String[] { "SJA_FDHYY_M_M_M_SUM_M_CTST", "SJC_FDHYY_M_M_M_SUM_M_CTST",
			"ZHP_FDHYY_M_M_M_SUM_M_CTST", "HPP_FDHYY_M_M_M_SUM_M_CTST", "ZJP_FDHYY_M_M_M_SUM_M_CTST",
			"SGY_FDHYY_M_M_M_SUM_M_CTST", "MMP_FDHYY_M_M_M_SUM_M_CTST", "YHP_FDHYY_M_M_M_SUM_M_CTST",
			"YLP_FDHYY_M_M_M_SUM_M_CTST", "JWP_FDHYY_M_M_M_SUM_M_CTST", "HHW_FDHYY_M_M_M_SUM_M_CTST",
			"JHP_FDHYY_M_M_M_SUM_M_CTST", "PHP_FDHYY_M_M_M_SUM_M_CTST", "GZH_FDHYY_M_M_M_SUM_M_CTST",
			"TSP_FDHYY_M_M_M_SUM_M_CTST", "DPP_FDHYY_M_M_M_SUM_M_CTST", "", ""

	};

	/**
	 * 耗用燃油-燃煤
	 */
	static final String[] HYRY_RM = new String[] { "", "" };

	/**
	 * 耗用天然气
	 */
	static final String[] HYTRQ = new String[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
			"HZL_HRQ_M_M_M_SUM_M_MANL", "GQL_HRQ_M_M_M_SUM_M_MANL" };

	/**
	 * 耗用天然气-燃煤
	 */
	static final String[] HYTRQ_RM = new String[] {
			"HZL_HRQ_M_M_M_SUM_M_MANL", "GQL_HRQ_M_M_M_SUM_M_MANL"
	};

	/**
	 * 报表左上角信息设置
	 */
	private static final String[] REPORT_DESC = new String[] { "CEC110-6000千瓦及以上电厂发电燃料消耗情况（年报）", "424998" };

	/**
	 * 更新IndicatorMapperReport5数据
	 */
	@Transactional
	public Boolean updateDatas(Long time) {
		Date date = new Date(time);
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		// 设置用户选择的月份的当月为结束时间
		long endTime = time;
		// 查询IndicatorMapperReport5表中当前时间戳是否存在数据
		List<IndicatorMapperReport5> historyReports = reportRepository.findByTimeOrderByRowIndexAsc(endTime);
		List<IndicatorMapperReport5> reportData = null;
		// 如果存在数据,则需要保留之前页面手动录入的数据。
		if (historyReports.size() != 0) {
			reportData = setUpdateReportData(historyReports, endTime);
		} else {// 如果不存在数据，则直接更新数据
			reportData = new ArrayList<>();
			for (int i = 0; i < ALL_ROW_NAME.length; i++) {
				IndicatorMapperReport5 report = new IndicatorMapperReport5();
				// 设置时间
				report.setTime(endTime);
				// 设置行号
				report.setRowIndex(i + 1);
				String[] rowName = ALL_ROW_NAME[i].split("##");
				// 设置指标名称
				report.setZbmc(rowName[0]);
				// 设置指标代码
				report.setZbdm(rowName[1]);
				// 设置组织机构代码
				if (rowName[2].equals("null")) {
					report.setZzjgdm("");
				} else {
					report.setZzjgdm(rowName[2]);
				}
				report.setFdl(0.0);
				report.setGdl(0.0);
				report.setBzml_hj(0.0);
				report.setBzml_mz(0.0);
				report.setBzml_yz(0.0);
				report.setBzml_trqz(0.0);
				report.setBzml_trqz(0.0);
				report.setBzml_qt(0.0);
				report.setHyry(0.0);
				report.setHytrq(0.0);
				reportData.add(report);
			}
			reportData = setUpdateReportData(reportData, endTime);
		}
		List<IndicatorMapperReport5> save = reportRepository.save(reportData);
		return save.size() > 0 ? true : false;
	}

	/**
	 * 从MapperData更新的数据，保存之前手动录入的数据。
	 * 
	 * @param historyReports
	 * @param beginTime
	 * @param endTime
	 * @param oldTime
	 * @return
	 */
	private List<IndicatorMapperReport5> setUpdateReportData(List<IndicatorMapperReport5> historyReports,
			Long endTime) {
		// 设置第1行数据
		// 6000千瓦及以上指标
		// 1. 计算发电煤耗的发电量
		List<String> indicatorCodes = Arrays.asList(FDL_ALL);
		List<IndicatorMapperData> findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double fdl = mapperDataListSum(findAll);
		// 2. 计算供电煤耗的供电量
		indicatorCodes = Arrays.asList(SCCYDL_ALL);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double sccydl = mapperDataListSum(findAll);
		// 3. 发电消耗标准煤量（吨）-合计
		indicatorCodes = Arrays.asList(BZML_HJ);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double bzml_hj = mapperDataListSum(findAll);
		// 4. 发电消耗标准煤量（吨）-煤折
		indicatorCodes = Arrays.asList(BZML_MZ);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double bzml_mz = mapperDataListSum(findAll);
		// 5. 发电消耗标准煤量（吨）-油折
		indicatorCodes = Arrays.asList(BZML_YZ);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double bzml_yz = mapperDataListSum(findAll);
		// 6. 发电消耗标准煤量（吨）-天然气折
		indicatorCodes = Arrays.asList(BZML_TRQZ);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double bzml_trq = mapperDataListSum(findAll);
		// 7. 发电消耗标准煤量（吨）-其它
		indicatorCodes = Arrays.asList(BZML_QT);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double bzml_qt = mapperDataListSum(findAll);
		// 8. 耗用原煤
		indicatorCodes = Arrays.asList(HYYM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hyym = mapperDataListSum(findAll);
		// 9. 耗用燃油
		indicatorCodes = Arrays.asList(HYRY);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hyyy = mapperDataListSum(findAll);
		// 10. 耗用天然气
		indicatorCodes = Arrays.asList(HYTRQ);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hytrq = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(0, historyReports, fdl, sccydl, bzml_hj, bzml_mz, bzml_yz, bzml_trq,
				bzml_qt, hyym, hyyy, hytrq);

		// 设置第2行数据
		// 6000千瓦及以上指标
		// 1. 计算发电煤耗的发电量
		indicatorCodes = Arrays.asList(FDL_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		fdl = mapperDataListSum(findAll);
		// 2. 计算供电煤耗的供电量
		indicatorCodes = Arrays.asList(SCCYDL_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		sccydl = mapperDataListSum(findAll);
		// 3. 发电消耗标准煤量（吨）-合计
		indicatorCodes = Arrays.asList(BZML_HJ_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		bzml_hj = mapperDataListSum(findAll);
		// 4. 发电消耗标准煤量（吨）-煤折
		indicatorCodes = Arrays.asList(BZML_MZ_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		bzml_mz = mapperDataListSum(findAll);
		// 5. 发电消耗标准煤量（吨）-油折
		indicatorCodes = Arrays.asList(BZML_YZ_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		bzml_yz = mapperDataListSum(findAll);
		// 6. 发电消耗标准煤量（吨）-天然气折
		indicatorCodes = Arrays.asList(BZML_TRQZ_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		bzml_trq = mapperDataListSum(findAll);
		// 7. 发电消耗标准煤量（吨）-其它
		indicatorCodes = Arrays.asList(BZML_QT_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		bzml_qt = mapperDataListSum(findAll);
		// 8. 耗用原煤
		indicatorCodes = Arrays.asList(HYYM_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		hyym = mapperDataListSum(findAll);
		// 9. 耗用燃油
		indicatorCodes = Arrays.asList(HYRY_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		hyyy = mapperDataListSum(findAll);
		// 10. 耗用天然气
		indicatorCodes = Arrays.asList(HYTRQ_RM);
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		hytrq = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(1, historyReports, fdl, sccydl, bzml_hj, bzml_mz, bzml_yz, bzml_trq,
				bzml_qt, hyym, hyyy, hytrq);

		for (int i = 0; i < FDL_ALL.length; i++) {
			IndicatorMapperData indicatorMapperData = null;
			// 1.计算发电煤耗的发电量
			fdl = 0.0;
			if (!FDL_ALL[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(FDL_ALL[i], endTime);
				fdl = indicatorMapperData.getValue();
			}
			// 2. 计算供电煤耗的供电量
			sccydl = 0.0;
			if (!SCCYDL_ALL[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(SCCYDL_ALL[i], endTime);
				sccydl = indicatorMapperData.getValue();
			}
			// 3. 发电消耗标准煤量（吨）-合计
			bzml_hj = 0.0;
			if (!BZML_HJ[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(BZML_HJ[i], endTime);
				bzml_hj = indicatorMapperData.getValue();
			}
			// 4. 发电消耗标准煤量（吨）-煤折
			bzml_mz = 0.0;
			if (!BZML_MZ[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(BZML_MZ[i], endTime);
				bzml_mz = indicatorMapperData.getValue();
			}
			// 5. 发电消耗标准煤量（吨）-油折
			bzml_yz = 0.0;
			if (!BZML_YZ[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(BZML_YZ[i], endTime);
				bzml_yz = indicatorMapperData.getValue();
			}
			// 6. 发电消耗标准煤量（吨）-天然气折
			bzml_trq = 0.0;
			if (!BZML_TRQZ[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(BZML_TRQZ[i], endTime);
				bzml_trq = indicatorMapperData.getValue();
			}
			// 7. 发电消耗标准煤量（吨）-其它
			bzml_qt = 0.0;
			if (!BZML_QT[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(BZML_QT[i], endTime);
				bzml_qt = indicatorMapperData.getValue();
			}
			// 8. 耗用原煤
			hyym = 0.0;
			if (!HYYM[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(HYYM[i], endTime);
				hyym = indicatorMapperData.getValue();
			}
			// 9. 耗用燃油
			hyyy = 0.0;
			if (!HYRY[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(HYRY[i], endTime);
				hyyy = indicatorMapperData.getValue();
			}
			// 10. 耗用天然气
			hytrq = 0.0;
			if (!HYTRQ[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(HYTRQ[i], endTime);
				hytrq = indicatorMapperData.getValue();
			}
			historyReports = setUpdateColumnData(3 + i, historyReports, fdl, sccydl, bzml_hj, bzml_mz, bzml_yz,
					bzml_trq, bzml_qt, hyym, hyyy, hytrq);
		}
		return historyReports;
	}

	/**
	 * 设置MapperDataList查询方法
	 */
	private List<IndicatorMapperData> findMapperDatas(List<String> indicatorCodes, Long beginTime, Long endTime) {
		List<SearchFilter> searchFilters = new ArrayList<>();
		searchFilters.add(SearchFilter.in("indicatorCode", indicatorCodes));
		searchFilters.add(SearchFilter.ge("time", beginTime));
		searchFilters.add(SearchFilter.le("time", endTime));
		Specification<IndicatorMapperData> buildAndSpec = JpaSearchUtils.buildAndSpec(searchFilters);
		List<IndicatorMapperData> findAll = mapperDataService.findAll(buildAndSpec);
		return findAll;
	}

	/**
	 * 设置MapperDataList求和方法
	 */
	private double mapperDataListSum(List<IndicatorMapperData> mapperDataList) {
		return mapperDataList.stream().mapToDouble(x -> x.getValue()).summaryStatistics().getSum();
	}

	/**
	 * 设置每行的年初生产能力、年末生产能力、本年减少能力和发电量
	 */
	private List<IndicatorMapperReport5> setUpdateColumnData(Integer index, List<IndicatorMapperReport5> historyReports,
			Double fdl, Double sccydl, Double bzml_hj, Double bzml_mz, Double bzml_yz, Double bzml_trqz, Double bzml_qt,
			Double hyym, Double hyry, Double hytrq) {
		// 1.计算发电煤耗的发电量
		historyReports.get(index).setFdl(fdl);
		// 2.计算供电煤耗的供电量
		historyReports.get(index).setGdl(historyReports.get(index).getFdl() - sccydl);
		// 3.发电消耗标准煤量（吨）-合计
		historyReports.get(index).setBzml_hj(bzml_hj);
		// 4. 发电消耗标准煤量（吨）-其中-煤折
		historyReports.get(index).setBzml_mz(bzml_mz);
		// 5. 发电消耗标准煤量（吨）-其中-油折
		historyReports.get(index).setBzml_yz(bzml_yz);
		// 6. 发电消耗标准煤量（吨）-其中-天然气折
		historyReports.get(index).setBzml_trqz(bzml_trqz);
		// 7. 发电消耗标准煤量（吨）-其中-其它
		historyReports.get(index).setBzml_qt(bzml_qt);
		// 8. 标准煤耗-发电
		if (fdl > 0.0) {
			historyReports.get(index).setBzmh_fd(historyReports.get(index).getBzml_hj() / fdl);
		} else {
			historyReports.get(index).setBzmh_fd(0.0);
		}
		// 9. 标准煤耗-供电
		if (historyReports.get(index).getGdl() > 0.0) {
			historyReports.get(index)
					.setBzmh_fd(historyReports.get(index).getBzml_hj() / historyReports.get(index).getGdl());
		} else {
			historyReports.get(index).setBzmh_gd(0.0);
		}
		// 10. 发电耗用-耗用原煤
		historyReports.get(index).setHyym(hyym);
		// 11. 发电耗用-耗用燃油
		historyReports.get(index).setHyry(hyry);
		// 12. 发电耗用-耗用天然气
		historyReports.get(index).setHytrq(hytrq*1315/10000);
		return historyReports;
	}

	/**
	 * 查询报表数据
	 * 
	 * @param time
	 * @return
	 */
	public List<IndicatorMapperReport5> findByTime(Long time) {
		return reportRepository.findByTimeOrderByRowIndexAsc(time);
	}

	/**
	 * 导出报表
	 */
	public HSSFWorkbook exportReport(List<IndicatorMapperReport5> reportDataList) {
		// 创建工作簿
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 创建工作表 工作表的名字叫helloWorld
		HSSFSheet sheet = workBook.createSheet("工作表1");
		// 创建左上角固定报表描述表格
		sheet = creatRepotDesc(workBook, sheet, reportDataList.get(0).getTime());

		// 创建下方主报表
		sheet = creatMainRepot(workBook, sheet, reportDataList);
		return workBook;
	}

	/**
	 * 创建左上角报表
	 * 
	 * @param sheet
	 * @param time
	 * @return
	 */
	public HSSFSheet creatRepotDesc(HSSFWorkbook workBook, HSSFSheet sheet, Long time) {
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);

		// 创建第1行
		HSSFRow row_1 = sheet.createRow(0);
		HSSFCell row_1_cell_1 = row_1.createCell(0);
		row_1_cell_1.setCellValue("*");
		HSSFCell row_1_cell_2 = row_1.createCell(1);
		row_1_cell_2.setCellValue("");
		HSSFCell row_1_cell_3 = row_1.createCell(2);
		row_1_cell_3.setCellValue("1.本表头供系统识别使用；\r\n" + "2.各单位根据具体情况填写单位代码、单位名称、填报人；\r\n" + "3.本表头报告期必须与此报表报告期一致；\r\n"
				+ "4.指标代码必须按照规律编；\r\n" + "5.不可改动报表格式。");
		// 创建第2行
		HSSFRow row_2 = sheet.createRow(1);
		HSSFCell row_2_cell_1 = row_2.createCell(0);
		HSSFRichTextString richString = new HSSFRichTextString("广东粤电集团有限公司");
		richString.applyFont(font);
		row_2_cell_1.setCellValue(richString);
		HSSFCell row_2_cell_2 = row_2.createCell(1);
		row_2_cell_2.setCellValue("（单位名称）");

		// 创建第3行
		HSSFRow row_3 = sheet.createRow(2);
		HSSFCell row_3_cell_1 = row_3.createCell(0);
		richString = new HSSFRichTextString("9808000001");
		richString.applyFont(font);
		row_3_cell_1.setCellValue(richString);
		HSSFCell row_3_cell_2 = row_3.createCell(1);
		row_3_cell_2.setCellValue("（单位代码）");

		// 创建第4行
		HSSFRow row_4 = sheet.createRow(3);
		HSSFCell row_4_cell_1 = row_4.createCell(0);
		richString = new HSSFRichTextString("统计专责");
		richString.applyFont(font);
		row_4_cell_1.setCellValue(richString);
		HSSFCell row_4_cell_2 = row_4.createCell(1);
		row_4_cell_2.setCellValue("（填报人）");

		// 创建第5行
		HSSFRow row_5 = sheet.createRow(4);
		HSSFCell row_5_cell_1 = row_5.createCell(0);
		row_5_cell_1.setCellValue(REPORT_DESC[0]);
		HSSFCell row_5_cell_2 = row_5.createCell(1);
		row_5_cell_2.setCellValue("（表名）");

		// 创建第6行
		HSSFRow row_6 = sheet.createRow(5);
		HSSFCell row_6_cell_1 = row_6.createCell(0);
		row_6_cell_1.setCellValue(REPORT_DESC[1]);
		HSSFCell row_6_cell_2 = row_6.createCell(1);
		row_6_cell_2.setCellValue("（表号）");

		// 创建第7行
		HSSFRow row_7 = sheet.createRow(6);
		HSSFCell row_7_cell_1 = row_7.createCell(0);
		row_7_cell_1.setCellValue("1");
		HSSFCell row_7_cell_2 = row_7.createCell(1);
		row_7_cell_2.setCellValue("（期别）");

		// 创建第8行
		Calendar cal = Calendar.getInstance();
		Date date = new Date(time);
		cal.setTime(date);
		HSSFRow row_8 = sheet.createRow(7);
		HSSFCell row_8_cell_1 = row_8.createCell(0);
		row_8_cell_1.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.YEAR))));
		HSSFCell row_8_cell_2 = row_8.createCell(1);
		row_8_cell_2.setCellValue("年");
		HSSFCell row_8_cell_3 = row_8.createCell(2);
		row_8_cell_3.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.MONTH) + 1)));
		HSSFCell row_8_cell_4 = row_8.createCell(3);
		row_8_cell_4.setCellValue("月");
		HSSFCell row_8_cell_5 = row_8.createCell(4);
		row_8_cell_5.setCellValue("");

		sheet.addMergedRegion(new CellRangeAddress(0, 6, 2, 2));

		return sheet;
	}

	/**
	 * 创建下方主要表格
	 * 
	 * @param sheet
	 * @param reportDataList
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public HSSFSheet creatMainRepot(HSSFWorkbook workBook, HSSFSheet sheet,
			List<IndicatorMapperReport5> reportDataList) {
		// 创建红色居中样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置居中样式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);
		cellStyle.setFont(font);

		// 创建第9行
		HSSFRow row_9 = sheet.createRow(8);
		HSSFCell row_9_cell_1 = row_9.createCell(0);
		row_9_cell_1.setCellValue("6000千瓦及以上电厂发电燃料消耗情况");

		// 创建第10行
		HSSFRow row_10 = sheet.createRow(9);
		HSSFCell row_10_cell_1 = row_10.createCell(0);
		row_10_cell_1.setCellValue("综合填报单位：  （章）");
		HSSFCell row_10_cell_2 = row_10.createCell(1);
		row_10_cell_2.setCellValue("");
		HSSFCell row_10_cell_3 = row_10.createCell(2);
		row_10_cell_3.setCellValue("");
		HSSFCell row_10_cell_4 = row_10.createCell(3);
		row_10_cell_4.setCellValue("");
		HSSFCell row_10_cell_5 = row_10.createCell(4);
		row_10_cell_5.setCellValue("");
		HSSFCell row_10_cell_6 = row_10.createCell(5);
		row_10_cell_6.setCellValue("");
		HSSFCell row_10_cell_7 = row_10.createCell(6);
		row_10_cell_7.setCellValue("");
		HSSFCell row_10_cell_8 = row_10.createCell(7);
		row_10_cell_8.setCellValue("YYYY");
		HSSFCell row_10_cell_9 = row_10.createCell(8);
		row_10_cell_9.setCellValue("");
		HSSFCell row_10_cell_10 = row_10.createCell(9);
		row_10_cell_10.setCellValue("");
		HSSFCell row_10_cell_11 = row_10.createCell(10);
		row_10_cell_11.setCellValue("");
		HSSFCell row_10_cell_12 = row_10.createCell(11);
		row_10_cell_12.setCellValue("");
		HSSFCell row_10_cell_13 = row_10.createCell(12);
		row_10_cell_13.setCellValue("");
		HSSFCell row_10_cell_14 = row_10.createCell(13);
		row_10_cell_14.setCellValue("");
		HSSFCell row_10_cell_15 = row_10.createCell(14);
		row_10_cell_15.setCellValue("");

		// 创建第11行
		HSSFRow row_11 = sheet.createRow(10);
		HSSFCell row_11_cell_1 = row_11.createCell(0);
		row_11_cell_1.setCellValue("电厂名称");
		HSSFCell row_11_cell_2 = row_11.createCell(1);
		row_11_cell_2.setCellValue("指标代码");
		HSSFCell row_11_cell_3 = row_11.createCell(2);
		row_11_cell_3.setCellValue("组织机构代码");
		HSSFCell row_11_cell_4 = row_11.createCell(3);
		row_11_cell_4.setCellValue("计算发电煤耗的发电量");
		HSSFCell row_11_cell_5 = row_11.createCell(4);
		row_11_cell_5.setCellValue("计算供电煤耗的供电量");
		HSSFCell row_11_cell_6 = row_11.createCell(5);
		row_11_cell_6.setCellValue("发电消耗标准煤量（吨）");
		HSSFCell row_11_cell_7 = row_11.createCell(10);
		row_11_cell_7.setCellValue("标准煤耗");
		HSSFCell row_11_cell_8 = row_11.createCell(12);
		row_11_cell_8.setCellValue("发电耗用原煤、燃油和天然气量等");

		// 创建第12行
		HSSFRow row_12 = sheet.createRow(11);
		HSSFCell row_12_cell_6 = row_12.createCell(5);
		row_12_cell_6.setCellValue("其       中");
		HSSFCell row_12_cell_7 = row_12.createCell(6);
		row_12_cell_7.setCellValue("本月");
		HSSFCell row_12_cell_11 = row_12.createCell(10);
		row_12_cell_11.setCellValue("（克/千瓦时）");
		HSSFCell row_12_cell_13 = row_12.createCell(12);
		row_12_cell_13.setCellValue("耗用原煤");
		HSSFCell row_12_cell_14 = row_12.createCell(13);
		row_12_cell_14.setCellValue("耗用燃油");
		HSSFCell row_12_cell_15 = row_12.createCell(14);
		row_12_cell_15.setCellValue("耗用天然气");

		// 创建第13行
		HSSFRow row_13 = sheet.createRow(12);
		HSSFCell row_13_cell_1 = row_13.createCell(3);
		row_13_cell_1.setCellValue("（万千瓦时）");
		HSSFCell row_13_cell_2 = row_13.createCell(4);
		row_13_cell_2.setCellValue("（万千瓦时）");
		HSSFCell row_13_cell_3 = row_13.createCell(6);
		row_13_cell_3.setCellValue("煤折");
		HSSFCell row_13_cell_4 = row_13.createCell(7);
		row_13_cell_4.setCellValue("油折");
		HSSFCell row_13_cell_5 = row_13.createCell(8);
		row_13_cell_5.setCellValue("天然气折");
		HSSFCell row_13_cell_6 = row_13.createCell(9);
		row_13_cell_6.setCellValue("其它");
		HSSFCell row_13_cell_11 = row_13.createCell(10);
		row_13_cell_11.setCellValue("发电");
		HSSFCell row_13_cell_12 = row_13.createCell(11);
		row_13_cell_12.setCellValue("供电");
		HSSFCell row_13_cell_13 = row_13.createCell(12);
		row_13_cell_13.setCellValue("（吨）");
		HSSFCell row_13_cell_14 = row_13.createCell(13);
		row_13_cell_14.setCellValue("（吨）");
		HSSFCell row_13_cell_15 = row_13.createCell(14);
		row_13_cell_15.setCellValue("（万立方米）");

		// 创建第14行
		HSSFRow row_14 = sheet.createRow(13);
		HSSFCell row_14_cell_1 = row_14.createCell(0);
		row_14_cell_1.setCellValue("甲");
		HSSFCell row_14_cell_2 = row_14.createCell(1);
		row_14_cell_2.setCellValue("1");
		HSSFCell row_14_cell_3 = row_14.createCell(2);
		row_14_cell_3.setCellValue("2");
		HSSFCell row_14_cell_4 = row_14.createCell(3);
		row_14_cell_4.setCellValue("3");
		HSSFCell row_14_cell_5 = row_14.createCell(4);
		row_14_cell_5.setCellValue("4");
		HSSFCell row_14_cell_6 = row_14.createCell(5);
		row_14_cell_6.setCellValue("5");
		HSSFCell row_14_cell_7 = row_14.createCell(6);
		row_14_cell_7.setCellValue("6");
		HSSFCell row_14_cell_8 = row_14.createCell(7);
		row_14_cell_8.setCellValue("7");
		HSSFCell row_14_cell_9 = row_14.createCell(8);
		row_14_cell_9.setCellValue("8");
		HSSFCell row_14_cell_10 = row_14.createCell(9);
		row_14_cell_10.setCellValue("9");
		HSSFCell row_14_cell_11 = row_14.createCell(10);
		row_14_cell_11.setCellValue("10");
		HSSFCell row_14_cell_12 = row_14.createCell(11);
		row_14_cell_12.setCellValue("11");
		HSSFCell row_14_cell_13 = row_14.createCell(12);
		row_14_cell_13.setCellValue("12");
		HSSFCell row_14_cell_14 = row_14.createCell(13);
		row_14_cell_14.setCellValue("13");
		HSSFCell row_14_cell_15 = row_14.createCell(14);
		row_14_cell_15.setCellValue("14");

		for (IndicatorMapperReport5 report : reportDataList) {
			HSSFRow row = sheet.createRow(report.getRowIndex() + ROW_INDEX);
			// 设置第1列
			HSSFCell cell_1 = row.createCell(0);
			cell_1.setCellValue(report.getZbmc());
			// 设置第2列
			HSSFCell cell_2 = row.createCell(1);
			cell_2.setCellValue(report.getZbdm());
			// 设置第3列
			HSSFCell cell_3 = row.createCell(2);
			cell_3.setCellValue(report.getZzjgdm());
			// 设置第4列
			HSSFCell cell_4 = row.createCell(3);
			cell_4.setCellValue(report.getFdl());
			// 设置第5列
			HSSFCell cell_5 = row.createCell(4);
			cell_5.setCellValue(report.getGdl());
			// 设置第6列
			HSSFCell cell_6 = row.createCell(5);
			cell_6.setCellValue(report.getBzml_hj());
			// 设置第7列
			HSSFCell cell_7 = row.createCell(6);
			cell_7.setCellValue(report.getBzml_mz());
			// 设置第8列
			HSSFCell cell_8 = row.createCell(7);
			cell_8.setCellValue(report.getBzml_yz());
			// 设置第9列
			HSSFCell cell_9 = row.createCell(8);
			cell_9.setCellValue(report.getBzml_trqz());
			// 设置第10列
			HSSFCell cell_10 = row.createCell(9);
			cell_10.setCellValue(report.getBzml_qt());
			// 设置第11列
			HSSFCell cell_11 = row.createCell(10);
			cell_11.setCellValue(report.getBzmh_fd());
			// 设置第12列
			HSSFCell cell_12 = row.createCell(11);
			cell_12.setCellValue(report.getBzmh_gd());
			// 设置第13列
			HSSFCell cell_13 = row.createCell(12);
			cell_13.setCellValue(report.getHyym());
			// 设置第14列
			HSSFCell cell_14 = row.createCell(13);
			cell_14.setCellValue(report.getHyry());
			// 设置第15列
			HSSFCell cell_15 = row.createCell(14);
			cell_15.setCellValue(report.getHytrq());
		}
		// 合并6000千瓦及以上电厂发电燃料消耗情况单元格
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 15));
		// 合并电厂名称单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 0, 0));
		// 合并指标代码单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 1, 1));
		// 合并组织结构代码单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 12, 2, 2));
		// 合并计算发电煤耗的发电量单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 3, 3));
		// 合并计算供电煤耗的供电量单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 4, 4));
		// 合并计算供电煤耗的供电量单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 5, 9));
		// 合并标准煤耗单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 10, 11));
		// 合并发电耗用原煤、燃油和天然气量等单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 12, 14));
		// 合并其 中单元格
		sheet.addMergedRegion(new CellRangeAddress(11, 11, 6, 9));

		return sheet;
	}
}
